package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.util.Collections;

import constants.GameTables;

import logic.DescSort;
import logic.PlayerNotFoundException;

import entities.Player;

/** The class that requests a ResultSet to the Database.
 * @author Team 1
 *
 */
public class Search {

	/** Return the name and the maximum score of all the players registered in the LOGIN table of an
	 * specified game table.
	 * @param table : the table from which the result sets are going to be processed.
	 * @return an ArrayList of all the player with their respective score in the specified table. If the
	 * player hasn't achieved any record yet it's maximum score will be set to zero.
	 */
	public static ArrayList<Player> playersMaxRecord(GameTables table) {
		ArrayList<Player> playerList = new ArrayList<Player>();
		try {
			//Opens the connection of the database resource.
			Connection conn = DataConnection.openConection();
			//Creates a statement.
			Statement s = conn.createStatement();
			//Executes a select from LOGIN table to get all the users that have been already registered.
			ResultSet rs = s.executeQuery("Select nick from LOGIN;");
			while(rs.next()) {
				//Adds to the arrayList the user with zero points set by default.
				playerList.add(new Player(rs.getString("nick"), 0));
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Creates a new statement.
			s = conn.createStatement();
			/*Executes a select from the specified table requesting all users that have already played the game
			and their respective score.*/
			rs = s.executeQuery("Select nick, max(score) from " + table.toString() + " group by nick order by score desc;");
			//If the query has a unclosed result set and while is not finished.
			while(rs.next()) {
				//Creates a new temporal player with the data obtained in the query.
				Player player = new Player(rs.getString("nick"), rs.getLong("max(score)"));
				//Search if the temporal player's nick is within list.
				int i = 0;
				boolean found = false;
				while(i < playerList.size() && !found) {
					//If the player is within the arrayList sets the list's user with the query's score.
					if(playerList.get(i).getName().equals(player.getName())) {
						playerList.get(i).setScore(player.getScore());
						found = true;
					}
					i++;
				}
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Closes the connection.
			conn.close();
			//Sorts the player ordered by their score from maximum to minimum.
			Collections.sort(playerList, new DescSort());
		} catch (SQLException e) {
			
		}
		//Returns the list of players.
		return playerList;
	}
	
	/** Return the name and all the score achieved of all the players registered in the LOGIN table of an
	 * specified game table.
	 * @param table : the table from which the result sets are going to be processed.
	 * @return an ArrayList of all the player with their respective scores in the specified table. If the
	 * player hasn't achieved any record yet it's unique score will be set to zero.
	 */
	public static ArrayList<Player> allPlayerRecord(GameTables table) {
		ArrayList<Player> playerList = new ArrayList<Player>();
		try {
			//Opens the connection of the database resource.
			Connection conn = DataConnection.openConection();
			//Creates a statement.
			Statement s = conn.createStatement();
			/*Executes select in order to get the nick names and all the records of all the players that
			have already played the game.*/
			ResultSet rs = s.executeQuery("Select nick, score from "+ table.toString() +" order by score desc;");
			//If the result set is unclosed and while is not finished.
			while(rs.next()) {
				//Add the record to the list.
				playerList.add(new Player(rs.getString("nick"), rs.getLong("score")));
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Creates an statement.
			s = conn.createStatement();
			//Gets the  all the nick of the users that have been registered into the application.
			rs = s.executeQuery("Select nick from LOGIN;");
			while(rs.next()) {
				boolean found = false;
				int i = 0;
				//Creates a temporal player with it's nick and zero points set by default. 
				Player playerToBeFound = new Player(rs.getString("nick"), 0);
				//Search if the player is with the list.
				while(!found && i < playerList.size()) {
					//If the player is found sets to true the found flag.
					if(playerToBeFound.getName().equals(playerList.get(i).getName())) {
						found = true;
					}
					i++;
				}
				//If the player hasn't been found.
				if(!found){
					//Adds the player to the list.
					playerList.add(playerToBeFound);
				}
			}
			//Closes the connection.
			conn.close();
		} catch (SQLException e) {

		}
		//Returns the list of players.
		return playerList;
	}
	
	/** Returns the maximum record of a specified player from the specified game.
	 * @param userName : The nick name of the user to be processed.
	 * @param table : The table of the game from which the data is going to be requested.
	 * @return a the maximum player record of the specified user from the specified game.
	 */
	public static Player playerMaxRecord(String userName, GameTables table) {
		//Creates a temporal player with it's nick and zero set score.
		Player player = new Player(userName, 0);
		try {
			//Opens the connection of the database resource.
			Connection conn = DataConnection.openConection();
			//Creates a variable statement.
			PreparedStatement s = conn.prepareStatement("Select nick, max(score) from " 
			+ table.toString() + " where nick = ? order by score desc;");
			//Sets the string with the variable.
			s.setString(1, userName);
			//Executes a select of the user's nick and maximum score of the specified game.
			ResultSet rs = s.executeQuery();
			//If the result set is unclosed.
			if(rs.next()) {
				//Set's temporal players score to the requested one.
				player.setScore(rs.getLong("max(score)"));
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Closes the connection.
			conn.close();
		} catch (SQLException e) {

		}
		//Returns the player record.
		return player;
	}
	
	/** Returns all the records of a specified player from the specified game.
	 * @param userName : The nick name of the user to be processed.
	 * @param table : The table of the game from which the data is going to be requested.
	 * @return an ArrayList of the player records of the specified user from the specified game.
	 */
	public static ArrayList<Player> allPlayerRecord(String userName, GameTables table) {
		ArrayList<Player> playerList = new ArrayList<Player>();
		try {
			//Opens the connection of the database resource.
			Connection conn = DataConnection.openConection();
			//Creates a variable statement.
			PreparedStatement s = conn.prepareStatement("Select nick, score from " + table.toString() 
					+ " where nick = ? order by score desc;");
			//Sets the statement with the variable.
			s.setString(1, userName);
			//Executes the select of all records the specified user and a specified game.
			ResultSet rs = s.executeQuery();
			//If the result set is unclosed and while is unfinished.
			while(rs.next()) {
				//Adds the record to the ArrayList.
				playerList.add(new Player(rs.getString("nick"), rs.getLong("score")));
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Closes the connection.
			conn.close();
			boolean found = false;
			int i = 0;
			//Creates a temporal user with it's nick and a zero set score.
			Player playerToBeFound = new Player(userName, 0);
			//Search if the player is within the list.
			while(!found && i < playerList.size()) {
				//If is found sets the found flag to true.
				if(playerToBeFound.getName().equals(playerList.get(i).getName())) {
					found = true;
				}
				i++;
			}
			//If it hasn't been found.
			if(!found){
				// Adds the temporal player to the list.
				playerList.add(playerToBeFound);
			}
		} catch (SQLException e) {
			
		} 
		//Return the list of the records of the specified player.
		return playerList;
	}
	
	/** Returns the maximum record for each the registered user.
	 * @return Returns the maximum record for each the registered user, if the player hasn't played
	 * any game yet it's maximum score will be set to zero.
	 */
	public static ArrayList<Player> globalRanking() {
		ArrayList<Player> members = new ArrayList<Player>();
		try {
			//Opens the connection to the database resource.
			Connection conn = DataConnection.openConection();
			//Creates a statement.
			Statement s = conn.createStatement();
			//Executes a select from Login in order to get all registered user.
			ResultSet rs = s.executeQuery("Select nick from LOGIN;");
			while(rs.next()) {
				//Adds all the users to the list.
				members.add(new Player(rs.getString("nick"), 0));
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Creates a new statement.
			s = conn.createStatement();
			//Executes a select of all the user's nicks and maximum score from the brick breaker game.
			rs = s.executeQuery("Select nick, max(score) from BRICKBREAKER group by nick order by max(score) desc;");
			//If the result set is unclosed and while is unfinished.
			while(rs.next()) {
				boolean found = false;
				int i = 0;
				//Search for the user within the user ArrayList.
				while(i < members.size() && !found) {
					//Creates a temporal player with the it's name and the maximum score achieved in that game.
					Player player = new Player(rs.getString("nick"), rs.getLong("max(score)"));
					//When the user is found.
					if(player.getName().equals(members.get(i).getName())) {
						//Sets its points to the ones achieved in this game.
						members.get(i).setScore(members.get(i).getScore() + player.getScore());
						//Sets the found flag to true.
						found = true;
					}
					i++;
				}
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Creates a new statement.
			s = conn.createStatement();
			//Executes a select of all the user's nicks and maximum score from the space invaders game.
			rs = s.executeQuery("Select nick, max(score) from SPACEINVADERS group by nick order by max(score) desc;");
			//If the result set is unclosed and while is unfinished.
			while(rs.next()) {
				boolean found = false;
				int i = 0;
				//Search for the user within the user ArrayList.
				while(i < members.size() & !found) {
					//Creates a temporal player with the it's name and the maximum score achieved in that game.
					Player player = new Player(rs.getString("nick"), rs.getLong("max(score)"));
					//When the user is found.
					if(player.getName().equals(members.get(i).getName())) {
						//Sets its points to the ones achieved in this game.
						members.get(i).setScore(members.get(i).getScore() + player.getScore());
						//Sets the found flag to true.
						found = true;
					}
					i++;
				}
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Closes the connection.
			conn.close();
			//Sorts the player ordered by their score from maximum to minimum.
			Collections.sort(members, new DescSort());
		} catch (SQLException e) {
			
		}
		return members;
	}
	/** Returns the maximum record of a specified registered user.
	 * @param userName : The nick name of the user to be processed.
	 * @return Returns the maximum record of a specified registered user, if the player hasn't played
	 * any game yet it's maximum score will be set to zero.
	 */
	public static Player globalRanking(String userName) {
		//Creates a temporal player.
		Player player = new Player(userName, 0);
		try {
			//Opens the connection of the database resource.
			Connection conn = DataConnection.openConection();
			//Creates a variable statement.
			PreparedStatement s = conn.prepareStatement("Select nick, max(score) from BRICKBREAKER where nick = ?;");
			//Sets the statement with the variable.
			s.setString(1, userName);
			//Executes the select of the nick and it maximum score from brick breaker game for a specified user.
			ResultSet rs = s.executeQuery();
			//If the players has already played the game.
			if(rs.next()) {
				//Sets the temporal players score with the requested one.
				player.setScore(rs.getLong("max(score)"));
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Creates a new variable statement.
			s = conn.prepareStatement("Select nick, max(score) from SPACEINVADERS where nick = ?;");
			//Sets the statement with the variable.
			s.setString(1, userName);
			//Executes the select of the nick and the max score from space invaders game for a specified user.
			rs = s.executeQuery();
			//If the player has already played the game.
			if(rs.next()) {
				//Adds to the player score the requested one.
				player.setScore(player.getScore() + rs.getLong("max(score)"));
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Closes the connection.
			conn.close();
		} catch (SQLException e) {
		
		}
		//Returns the specified player's record.
		return player;
	}
	
	/** Executes a query to check if the user is registered in the database.
	 * @param userName : The nick of the user to be processed.
	 * @param password : The password of the user to be processed.
	 * @throws PlayerNotFoundException if the player is not in stored in the database throws and exception.
	 */
	public static void isRegistered(String userName, String password) throws PlayerNotFoundException {
		try {
			//Opens a connection to the database resource.
			Connection conn = DataConnection.openConection();
			//Creates a variable statement.
			PreparedStatement s = conn.prepareStatement("Select nick from LOGIN where nick = ? and pass = ?;");
			//Sets the statement with a variable.
			s.setString(1, userName);
			//Sets the statement with a second variable.
			s.setString(2, password);
			//Execute a select from login for a specified nick and password.
			ResultSet rs = s.executeQuery();
			//If the result set is closed.
			if(!rs.next()) {
				//Throws a PlayerNotFoundExpection.
				throw new PlayerNotFoundException();
			}
			//Closes the result set.
			rs.close();
			//Closes the statement.
			s.close();
			//Closes the connection.
			conn.close();
		} catch (SQLException e) {
		
		}
	}
}